1.정리

• 관계가 없는 테이블간의 조인시 카테시안 곱만큼의 로우가 생성된다.

• 1이란 숫자는 아무리 여러 번을 곱하여도 1이라는 수학적인 성질을 이용하여
  참조해야 할 여러 개의 테이블간의 연결을 하나의 SQL로 합칠 수가 있을 것이다.

• SQL의 별도 수행은 잦은 DBMS 호출을 초래하여 시스템 오버헤드의 주범이 된다.

• 조인은 어느 한 집합만 공집합이 되더라도 전체가 공집합이 되어 버리므로
  다른 성공한 집합까지 실패하게 된다.

• SQL에서 OUTER 조인을 하고자 하는 임의의 조인 컬럼에 반드시 성공하는 집합의 컬럼을
  결과에 영향을 미치지 않도록 가공하여 추가하면 어느 한 집합이 공집합이 되더라도
  수행결과는 정상적으로 추출된다.

2.테이블 설계

(1)고객(CUSTOMER)
고객번호(CUST_NO/PK), 고객명(CUST_NAME)

(2)사원(EMPLOYEE)
사원번호(EMP_NO/PK), 부서(DEPT), 호봉(SALEGRADE)

(3)예산(BUDGET)
부서(DEPT_NO/PK1), 계정과목(ACCOUNT/PK2), 예산년도(BUDGET_YR/PK3), 편성금액(BUDGET_AMT)

3.CREATE TABLE

CREATE TABLE ORA9.CUSTOMER (

COMMENT ON TABLE ORA9.CUSTOMER IS '고객';

COMMENT ON COLUMN ORA9.CUSTOMER.CUST_NO IS '고객번호';

COMMENT ON COLUMN ORA9.CUSTOMER.CUST_NAME IS '고객명';

ALTER TABLE ORA9.CUSTOMER ADD (

CREATE TABLE ORA9.EMPLOYEE (

COMMENT ON TABLE ORA9.EMPLOYEE IS '사원';

COMMENT ON COLUMN ORA9.EMPLOYEE.EMP_NO IS '사원번호';

COMMENT ON COLUMN ORA9.EMPLOYEE.DEPT IS '부서';

COMMENT ON COLUMN ORA9.EMPLOYEE.SALEGRADE IS '호봉';

ALTER TABLE ORA9.EMPLOYEE ADD (

CREATE TABLE ORA9.BUDGET (

COMMENT ON TABLE ORA9.BUDGET IS '예산';

COMMENT ON COLUMN ORA9.BUDGET.DEPT_NO IS '부서';

COMMENT ON COLUMN ORA9.BUDGET.ACCOUNT IS '계정과목';

COMMENT ON COLUMN ORA9.BUDGET.BUDGET_YR IS '예산년도';

COMMENT ON COLUMN ORA9.BUDGET.BUDGET_AMT IS '편성금액';

ALTER TABLE ORA9.BUDGET ADD (

4.INSERT

INSERT INTO ORA9.CUSTOMER VALUES('0001','고객1');

INSERT INTO ORA9.CUSTOMER VALUES('0002','고객2');

INSERT INTO ORA9.CUSTOMER VALUES('0003','고객3');

INSERT INTO ORA9.CUSTOMER VALUES('0004','고객4');

INSERT INTO ORA9.CUSTOMER VALUES('0005','고객5');

COMMIT;

INSERT INTO ORA9.EMPLOYEE VALUES('0001','개발','1');

INSERT INTO ORA9.EMPLOYEE VALUES('0002','영업','2');

INSERT INTO ORA9.EMPLOYEE VALUES('0003','마케팅','1');

INSERT INTO ORA9.EMPLOYEE VALUES('0004','경영','3');

INSERT INTO ORA9.EMPLOYEE VALUES('0005','지원','1');

COMMIT;

INSERT INTO ORA9.BUDGET VALUES('0001','1101','2009',3000000);

INSERT INTO ORA9.BUDGET VALUES('0001','1101','2008',2500000);

INSERT INTO ORA9.BUDGET VALUES('0001','1102','2009',3500000);

INSERT INTO ORA9.BUDGET VALUES('0001','1102','2008',2800000);

INSERT INTO ORA9.BUDGET VALUES('0002','1101','2009',2500000);

INSERT INTO ORA9.BUDGET VALUES('0002','1101','2008',2000000);

INSERT INTO ORA9.BUDGET VALUES('0002','1102','2009',4500000);

INSERT INTO ORA9.BUDGET VALUES('0002','1102','2008',3600000);

INSERT INTO ORA9.BUDGET VALUES('0003','1101','2009',3200000);

INSERT INTO ORA9.BUDGET VALUES('0003','1101','2008',2400000);

INSERT INTO ORA9.BUDGET VALUES('0003','1102','2009',3300000);

INSERT INTO ORA9.BUDGET VALUES('0003','1102','2008',2700000);

INSERT INTO ORA9.BUDGET VALUES('0004','1101','2009',4400000);

INSERT INTO ORA9.BUDGET VALUES('0004','1101','2008',3800000);

INSERT INTO ORA9.BUDGET VALUES('0004','1102','2009',2500000);

INSERT INTO ORA9.BUDGET VALUES('0004','1102','2008',1800000);

INSERT INTO ORA9.BUDGET VALUES('0005','1101','2009',3300000);

INSERT INTO ORA9.BUDGET VALUES('0005','1101','2008',2900000);

INSERT INTO ORA9.BUDGET VALUES('0005','1102','2009',3700000);

INSERT INTO ORA9.BUDGET VALUES('0005','1102','2008',3000000);

COMMIT;

5.결과

(1)SQL 분리시

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO1
2
3 (P_CUST_NO ORA9.CUSTOMER.CUST_NO%TYPE,
4 P_EMP_NO ORA9.EMPLOYEE.EMP_NO%TYPE,
5 P_DEPT_NO ORA9.BUDGET.DEPT_NO%TYPE
6 )
7
8 IS
9
10 CURSOR CUSTOMER_CSR IS
11 SELECT CUST_NAME
12 FROM ORA9.CUSTOMER
13 WHERE CUST_NO = P_CUST_NO;
14
15 CURSOR EMPLOYEE_CSR IS
16 SELECT DEPT
17 FROM ORA9.EMPLOYEE
18 WHERE EMP_NO = P_EMP_NO;
19
20 CURSOR BUDGET_CSR IS
21 SELECT BUDGET_AMT
22 FROM ORA9.BUDGET
23 WHERE DEPT_NO = P_DEPT_NO
24 AND ACCOUNT = '1101'
25 AND BUDGET_YR = TO_CHAR(SYSDATE, 'YYYY');
26
27 C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
28
29 C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
30
31 C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
32
33 BEGIN
34
35 OPEN CUSTOMER_CSR;
36
37 LOOP
38
39 FETCH CUSTOMER_CSR INTO C_CUST_NM;
40
41 EXIT WHEN CUSTOMER_CSR%NOTFOUND;
42
43 DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
44
45 OPEN EMPLOYEE_CSR;
46
47 LOOP
48
49 FETCH EMPLOYEE_CSR INTO C_DEPT;
50
51 EXIT WHEN EMPLOYEE_CSR%NOTFOUND;
52
53 DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
54
55 OPEN BUDGET_CSR;
56
57 LOOP
58
59 FETCH BUDGET_CSR INTO C_BUDGET_AMT;
60
61 EXIT WHEN BUDGET_CSR%NOTFOUND;
62
63 DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
64
65 END LOOP;
66
67 CLOSE BUDGET_CSR;
68
69 END LOOP;
70
71 CLOSE EMPLOYEE_CSR;
72
73 END LOOP;
74
75 CLOSE CUSTOMER_CSR;
76
77 EXCEPTION
78
79 WHEN OTHERS THEN
80
81 DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
82
83 END;
84 /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO1('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.

(2)SQL 통합시

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO2
2
3 (P_CUST_NO ORA9.CUSTOMER.CUST_NO%TYPE,
4 P_EMP_NO ORA9.EMPLOYEE.EMP_NO%TYPE,
5 P_DEPT_NO ORA9.BUDGET.DEPT_NO%TYPE
6 )
7
8 IS
9
10 CURSOR NOREL_JOIN_CSR IS
11 SELECT CUST_NAME
12 ,DEPT
13 ,BUDGET_AMT
14 FROM ORA9.CUSTOMER, ORA9.EMPLOYEE, ORA9.BUDGET
15 WHERE CUST_NO = P_CUST_NO
16 AND EMP_NO = P_EMP_NO
17 AND DEPT_NO = P_DEPT_NO
18 AND ACCOUNT = '1101'
19 AND BUDGET_YR = TO_CHAR(SYSDATE, 'YYYY');
20
21 C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
22
23 C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
24
25 C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
26
27 BEGIN
28
29 OPEN NOREL_JOIN_CSR;
30
31 LOOP
32
33 FETCH NOREL_JOIN_CSR INTO C_CUST_NM,C_DEPT,C_BUDGET_AMT;
34
35 EXIT WHEN NOREL_JOIN_CSR%NOTFOUND;
36
37 DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
38
39 DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
40
41 DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
42
43 END LOOP;
44
45 CLOSE NOREL_JOIN_CSR;
46
47 EXCEPTION
48
49 WHEN OTHERS THEN
50
51 DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
52
53 END;
54 /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO2('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.

(3)OUTER JOIN으로 SQL 통합시(실패)

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO3
2
3 (P_CUST_NO ORA9.CUSTOMER.CUST_NO%TYPE,
4 P_EMP_NO ORA9.EMPLOYEE.EMP_NO%TYPE,
5 P_DEPT_NO ORA9.BUDGET.DEPT_NO%TYPE
6 )
7
8 IS
9
10 CURSOR NOREL_JOIN_CSR IS
11 SELECT CUST_NAME
12 ,DEPT
13 ,BUDGET_AMT
14 FROM ORA9.CUSTOMER, ORA9.EMPLOYEE, ORA9.BUDGET
15 WHERE CUST_NO = P_CUST_NO
16 AND EMP_NO = P_EMP_NO
17 AND DEPT_NO(+) = P_DEPT_NO
18 AND ACCOUNT(+) = '1100'
19 AND BUDGET_YR(+) = TO_CHAR(SYSDATE, 'YYYY');
20
21 C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
22
23 C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
24
25 C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
26
27 BEGIN
28
29 OPEN NOREL_JOIN_CSR;
30
31 LOOP
32
33 FETCH NOREL_JOIN_CSR INTO C_CUST_NM,C_DEPT,C_BUDGET_AMT;
34
35 EXIT WHEN NOREL_JOIN_CSR%NOTFOUND;
36
37 DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
38
39 DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
40
41 DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
42
43 END LOOP;
44
45 CLOSE NOREL_JOIN_CSR;
46
47 EXCEPTION
48
49 WHEN OTHERS THEN
50
51 DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
52
53 END;
54 /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO3('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다. \--> 결과값이 출력되지 않았음

(4)OUTER JOIN으로 SQL 통합시(성공)

CREATE OR REPLACE PROCEDURE NOREL_JOIN_PRO4
2
3 (P_CUST_NO ORA9.CUSTOMER.CUST_NO%TYPE,
4 P_EMP_NO ORA9.EMPLOYEE.EMP_NO%TYPE,
5 P_DEPT_NO ORA9.BUDGET.DEPT_NO%TYPE
6 )
7
8 IS
9
10 CURSOR NOREL_JOIN_CSR IS
11 SELECT CUST_NAME
12 ,DEPT
13 ,BUDGET_AMT
14 FROM ORA9.CUSTOMER, ORA9.EMPLOYEE, ORA9.BUDGET
15 WHERE CUST_NO = P_CUST_NO
16 AND EMP_NO = P_EMP_NO
17 AND DEPT_NO(+) = P_DEPT_NO || SUBSTR(EMP_NO,0,0)
18 AND ACCOUNT(+) = '1100'
19 AND BUDGET_YR(+) = TO_CHAR(SYSDATE, 'YYYY');
20
21 C_CUST_NM ORA9.CUSTOMER.CUST_NAME%TYPE;
22
23 C_DEPT ORA9.EMPLOYEE.DEPT%TYPE;
24
25 C_BUDGET_AMT ORA9.BUDGET.BUDGET_AMT%TYPE;
26
27 BEGIN
28
29 OPEN NOREL_JOIN_CSR;
30
31 LOOP
32
33 FETCH NOREL_JOIN_CSR INTO C_CUST_NM,C_DEPT,C_BUDGET_AMT;
34
35 EXIT WHEN NOREL_JOIN_CSR%NOTFOUND;
36
37 DBMS_OUTPUT.PUT_LINE('고객명 : ' || C_CUST_NM);
38
39 DBMS_OUTPUT.PUT_LINE('부서 : ' || C_DEPT);
40
41 DBMS_OUTPUT.PUT_LINE('편성금액 : ' || C_BUDGET_AMT);
42
43 END LOOP;
44
45 CLOSE NOREL_JOIN_CSR;
46
47 EXCEPTION
48
49 WHEN OTHERS THEN
50
51 DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생');
52
53 END;
54 /

프로시저가 생성되었습니다.

SET SERVEROUTPUT ON;

EXECUTE NOREL_JOIN_PRO4('0001','0001','0001');

PL/SQL 처리가 정상적으로 완료되었습니다.

6. 주석

NOREL_JOIN_PRO3의 경우는 만족하는 값이 없으면 OUTER-JOIN 을 했다 하더라도 항상 공집합이다.

즉, DEPT_NO(+) = P_DEPT_NO 는 DEPT_NO = P_DEPT_NO 와 같은 결과를 낸다.

OUTER-JOIN 은 반드시 성공한 어떤 집합과 연결을 할 때만 의미가 있다.

즉, 상수값과는 OUTER-JOIN을 해도 결과는 달라지지 않는다.

반면 NOREL_JOIN_PRO4 에서는

CUST_NO = P_CUST_NO, EMP_NO = P_EMP_NO 를 만족하는 집합이 BUDGET 테이블과 OUTER-JOIN을 시도하는데,

DEPT_NO(+) = P_DEPT_NO || SUBSTR(EMP_NO,0,0) 조건을 보면

SUBSTR(EMP_NO,0,0)은 NULL 값이 되므로 P_DEPT_NO에 붙이더라도 그 결과에는 영향을 미치지 않는다.

이 구문의 추가로 인해 EMPLOYEE 테이블의 수행 결과는 BUDGET 테이블의 수행 결과에 영향을 받지 않는다.